#!/usr/bin/perl -w
use Getopt::Long;
use FindBin;
use strict;

#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

#	Usage

#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888


my $usage = <<'USAGE';

USAGE:

    panda_setup.pl --user USER_NAME [OPTIONS]...  DB_NAME


    MANDATORY ARGUMENTS:

        --user USER_NAME     the postgreSQL user name of the owner/panda administrator


    OPTIONAL ARGUMENTS:

        [--host STRING]      the postgreSQL database server
        [--password STRING]  the postgreSQL database password

    These two options only need to be specified if you are re-initializing:

        [--clear_first]      current sequence/accessions removed
        [--recreate_db]      all data removed
                                    including archived sequences and the update logs.

        [--help]


RESULTS:


    This script creates the panda system with DB_NAME as the PostgreSQL database
                behind panda.

USAGE

#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

#	Get options

#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888


my $user			= 'postgres';
my $password		= '';
my $host			= '';
my $clear_first;
my $recreate_db;

my $help			= '';
my $dbname			= '';

GetOptions (
			
			'help'				=> \$help,
			'user=s'			=> \$user,
			'password'			=> \$password,
			'host=s'			=> \$host,
			'clear_first' 		=> \$clear_first,
			'recreate_db'		=> \$recreate_db,
			);


die $usage if ($help);
$dbname			= $ARGV[0] if ($ARGV[0]);
die "\nError:\n\tMissing database name.\n$usage" unless ($dbname);
die "\nError:\n\tMissing user name.\n$usage" unless ($user);
$host		= '--host=' 	. $host if ($host);
$password	= '--password '			if ($password);
my $username= "--username $user";



#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

#	Does the database already exist? If not recreate anyway

#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888
my $existing_db = `psql -l -q $host $username $password`;
#my $existing_db = `psql -t -A -h db -U postgres -c "select datname from pg_catalog.pg_database" template1`;
my $db_exists = ($existing_db =~ /$dbname[ \|]/);
$recreate_db = 1 unless ($db_exists);

if (!$recreate_db && !$clear_first)
{
	die
	"Error:\n".
	"\t$dbname already exists. \n".
	"\tSpecify --clear_first or --recreate_db to reinitialize\n";
}


#declarations
sub create_db;
sub clear_db;
sub create_tables;
sub repopulate_tables;


#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

#	main

#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888


if (defined $recreate_db)
{
	print STDERR "\tCreate Database...\n";
	create_db();
}


else
{
	clear_db();
}


create_tables();

repopulate_tables();

print STDERR "\n\tThe panda database [$dbname] has been setup successfully!\n\n";







#_________________________________________________________________________________________

#	create_db

#_________________________________________________________________________________________
sub create_system_tables
{
	open(FH_SQLCOPY, "| psql -q $host $username $password $dbname");
	print FH_SQLCOPY <<"PL/SQLCMD";

-- create table. Some of these will fail if they already exist

-- --------------------------------------------------------
-- database_sources
CREATE TABLE database_sources
(
    name                     TEXT,
    description              TEXT,
    depends                  TEXT,
    date_added               TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    remote_url               TEXT,
    db_last_update           TIMESTAMP DEFAULT '01/01/1900',
    db_update_interval       INTERVAL,
    local_file               TEXT,
    local_file_date          TIMESTAMP DEFAULT '01/01/1900',
    local_file_MD5           TEXT DEFAULT '',
    check_up_to_date_cmds    TEXT,
    pre_process_cmds         TEXT,
    run_panda                BOOLEAN DEFAULT 'TRUE',
    panda_param_fasta_file   TEXT,
    panda_param_regex        TEXT,
    panda_param_db_name    TEXT,
    panda_param_update_stats BOOLEAN,
    panda_param_misc         TEXT,
    post_process_cmds        TEXT,
    update_failed_date       TIMESTAMP DEFAULT '01/01/1900',
    status                   TEXT  DEFAULT 'not updated'
);
GRANT SELECT ON TABLE database_sources         TO GROUP select_panda;

-- --------------------------------------------------------
-- database_sources
CREATE TABLE panda_parameters
(
    panda_cmd_line          TEXT,
    panda_scripts_dir       TEXT,
    db_scripts_dir          TEXT,
    panda_working_dir       TEXT,
    seq_dir                 TEXT,
    panda_dbname           TEXT,
    panda_usr_name          TEXT,
    panda_remote_host       TEXT,
	logfile                 TEXT
);

GRANT SELECT ON TABLE panda_parameters         TO GROUP select_panda;

CREATE TABLE updates_log
(
    name                                    TEXT,
    cnt_fasta_seq_parsed                    INTEGER,
    cnt_fasta_acc_sans_taxa                 INTEGER,
    cnt_fasta_acc_sans_codes                INTEGER,
    cnt_fasta_pi_duplicates                 INTEGER,
    cnt_acc_same_origin_in_panda            INTEGER,
    cnt_acc_obsolete                        INTEGER,
    cnt_acc_additional                      INTEGER,
    cnt_secids_additional                   INTEGER,
    cnt_seq_in_panda                        INTEGER,
    cnt_seq_obsolete                        INTEGER,
    cnt_seq_additional                      INTEGER,
    time_cnt_seq                            REAL,
    time_pg_import_acc_hash                 REAL,
    time_parse_acc                          REAL,
    time_pg_import_obsolete_acc_pi          REAL,
    time_pg_remove_obsolete_acc             REAL,
    time_pg_add_acc                         REAL,
    time_pg_recreate_acc_indices            REAL,
    time_pg_add_secids                      REAL,
    time_pg_recreate_secids_indices         REAL,
    time_pg_import_panda_seq_pi             REAL,
    time_compare_seq_pi                     REAL,
    time_check_pi_collisions                REAL,
    time_pg_add_seq                         REAL,
    time_pg_recreate_seq_indices            REAL,
    time_pg_export_new_seq_pi               REAL,
    time_pg_add_new_seq_pi                  REAL,
    time_pg_export_acc_pi                   REAL,
    time_compare_acc_seq_pi                 REAL,
    time_pg_remove_orphaned_seq             REAL,
    time_pg_sync_pi_dependents              REAL,
    time_pg_vacuum                          REAL,
    time_total                              REAL,
    parameters                              TEXT,
    time_update                             TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

GRANT SELECT ON TABLE updates_log         TO GROUP select_panda;

-- 88888888888888888888888888888888888888888888888888888888

-- archival

-- 88888888888888888888888888888888888888888888888888888888

CREATE TABLE archived_sequences
(
    len                 INTEGER,
    seq_hash            OID[4],
    sequence            TEXT,
    seq_date_added      TIMESTAMP,
    seq_date_archived   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE archived_accessions
(
    tax_id              OID,
    acc_hash            OID[4],
    prot_id             TEXT NOT NULL,
    acc_name            TEXT,
    db_origin           TEXT NOT NULL,
    acc_text            TEXT,
    acc_date_added      TIMESTAMP,
    acc_date_archived   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE archived_domains
(
    hmm_name            TEXT,
    dom_code            INTEGER,
    thres_true          DOUBLE PRECISION,
    thres_maybe         DOUBLE PRECISION,
    thres_repeat        DOUBLE PRECISION,
    thres_subfamily     DOUBLE PRECISION,
    definition          TEXT,
    description         TEXT,
    binding_catalysis   TEXT,
    dom_date_archived   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE archived_hmms
(
    hmm_name            TEXT,
    hmm_acc             TEXT,
    dom_code            INTEGER,
    hmm_file            TEXT,
    hmm_hash            OID[4],
    hmm_db_origin       TEXT,
    aln_method          TEXT,
    db_state            TEXT,
    author              TEXT,
    hmm                 TEXT,
    hmm_date_added      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    hmm_date_archived   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);



PL/SQLCMD

}


sub create_group
{
	# will create warnings if already exists so pipe to dev null
	system ("psql -l -q $host $username $password $dbname ".
			"-c \"CREATE GROUP select_panda WITH USER $user\" 2> /dev/null");
	
	system ("psql -l -q $host $username $password $dbname ".
			"-c \"ALTER GROUP select_panda ADD USER $user\" 2> /dev/null");
}


#_________________________________________________________________________________________

#	create_db

#_________________________________________________________________________________________
sub create_db
{
	if ($db_exists)
	{
		system "dropdb -q $host $username $password $dbname" and die;
	}
	print STDERR"createdb -q $host $username $password $dbname";
	system "createdb -q $host $username $password $dbname" and die;
	create_group();
	create_system_tables();

}


#_________________________________________________________________________________________

#	clear_db

#_________________________________________________________________________________________
sub clear_db
{
	open(FH_SQLCOPY, "| psql -q $host $username $password $dbname");
	print FH_SQLCOPY <<'PL/SQLCMD';
    --TRUNCATE database_sources;
	--TRUNCATE panda_parameters;
	DROP TABLE sequences;
	DROP TABLE accessions;
	DROP TABLE secondary_ids;
	DROP TABLE taxa;
	DROP TABLE taxa_divisions;
	DROP TABLE new_sequences;
	DROP TABLE pubmed_ids;
	DROP TABLE domains;
	DROP TABLE hmms;
	DROP TABLE new_hmms;
	DROP TABLE domains_per_protein;
	DROP TABLE sequences_linked_tables;
	DROP TABLE archived_sequences;
	DROP TABLE archived_accessions;
	DROP TABLE archived_domains;
	DROP TABLE archived_hmms;
PL/SQLCMD
}

#_________________________________________________________________________________________

#	create_tables

#_________________________________________________________________________________________
sub create_tables
{

	open(FH_SQLCOPY, "| psql -q $host $username $password $dbname");
	print FH_SQLCOPY <<'PL/SQLCMD';
-- --------------------------------------------------------
-- sequences
CREATE TABLE sequences
(
    pi                  OID,
    pi_reserved         OID,
    len                 INTEGER,
    seq_hash            OID[4],
    sequence            TEXT,
    seq_date_added      TIMESTAMP
);

ALTER TABLE sequences ALTER sequence SET STATISTICS 0;
ALTER TABLE sequences ALTER pi_reserved SET STATISTICS 0;

CREATE SEQUENCE s_pi START 1;
-- --------------------------------------------------------
-- accessions
CREATE TABLE accessions
(
    pi                  OID,
    acc_pi_reserved     OID,
    accid               OID,
    accid_reserved      OID,
    tax_id              OID,
    acc_hash            OID[4],
    prot_id             TEXT NOT NULL,
    acc_name            TEXT,
    db_origin           TEXT NOT NULL,
    acc_text            TEXT,
    acc_date_added      TIMESTAMP
);

ALTER TABLE accessions ALTER acc_pi_reserved SET STATISTICS 0;
ALTER TABLE accessions ALTER accid_reserved SET STATISTICS 0;
CREATE SEQUENCE s_accid START 1;

-- --------------------------------------------------------
-- linked_ids
CREATE TABLE secondary_ids
(
    pi                      OID,
    secid_pi_reserved       OID,
    secid_accid             OID,
    secid_accid_reserved    OID,
    db_code             TEXT NOT NULL,
    secondary_id        TEXT NOT NULL,
    secid_db_origin     TEXT NOT NULL
);



-- --------------------------------------------------------
-- taxa
CREATE TABLE taxa
(
    tax_id              OID,
    tax_name            TEXT,
    division            INTEGER,
    species             OID,
    genus               OID,
    family              OID,
    tax_order           OID,
    class               OID,
    phylum              OID,
    kingdom             OID,
    superkingdom        OID
);

-- --------------------------------------------------------
-- taxid_from_gi
CREATE TABLE taxid_from_gi
(
    gi					INTEGER,
    tax_id				OID
);

-- --------------------------------------------------------
-- taxid_from_taxname
CREATE TABLE taxid_from_taxname
(
    tax_name_hash		OID[4],
    tax_id  			OID
);

-- --------------------------------------------------------
-- taxa_divisions
CREATE TABLE taxa_divisions
(
    division            INTEGER,
    division_name       TEXT
);

-- --------------------------------------------------------
-- new_sequences
CREATE TABLE new_sequences
(
    pi                  OID
);


-- --------------------------------------------------------
-- pubmed_ids
CREATE TABLE pubmed_ids
(
    pubmed_id           INTEGER,
    pmid_seq_hash       OID[4],
    pi                  OID,
    pmid_pi_reserved    OID,
    pmid_db_origin      INTEGER
);


-- --------------------------------------------------------
-- domains
CREATE TABLE domains
(
    hmm_name            TEXT,
    dom_code            INTEGER,
    thres_true          DOUBLE PRECISION,
    thres_maybe         DOUBLE PRECISION,
    thres_repeat        DOUBLE PRECISION,
    thres_subfamily     DOUBLE PRECISION,
    definition          TEXT,
    description         TEXT,
    binding_catalysis   TEXT
);


-- --------------------------------------------------------
-- hmms
CREATE TABLE hmms
(
    hmm_name            TEXT,
    hmm_acc             TEXT,
    dom_code            INTEGER,
    hmm_file            TEXT,
    hmm_hash            OID[4],
    hmm_db_origin       TEXT,
    aln_method          TEXT,
    db_state            TEXT,
    author              TEXT,
    hmm                 TEXT,
    hmm_date_added      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


-- --------------------------------------------------------
-- new_hmms
CREATE TABLE new_hmms
(
    dom_code            INTEGER
);


-- --------------------------------------------------------
-- domains_per_protein
CREATE TABLE domains_per_protein
(
    pi                  OID,
    dom_pi_reserved     OID,
    dom_code            INTEGER,
    dom_name            TEXT,
    start               INTEGER,
    finish              INTEGER,
    p_value             DOUBLE PRECISION,
    confidence          INTEGER
);


-- --------------------------------------------------------
-- sequences_linked_tables
CREATE TABLE sequences_linked_tables
(
    table_name          TEXT
);


-- 88888888888888888888888888888888888888888888888888888888

-- Indices

-- 88888888888888888888888888888888888888888888888888888888

-- --------------------------------------------------------
-- indices for table joins
CREATE UNIQUE INDEX i_sequences__pi         ON sequences            (pi);
CREATE INDEX        i_acc__taxid            ON accessions           (tax_id);
CREATE INDEX        i_acc__pi	            ON accessions           (pi);
CREATE INDEX        i_secid__pi      		ON secondary_ids        (pi);
CREATE UNIQUE INDEX i_taxa__taxid           ON taxa                 (tax_id);
CREATE UNIQUE INDEX i_taxa__taxname         ON taxa                 (tax_name);
CREATE INDEX        i_pubmed__pi            ON pubmed_ids           (pi);
CREATE UNIQUE INDEX i_domains__dom_code     ON domains              (dom_code);
CREATE UNIQUE INDEX i_hmms__dom_code        ON hmms                 (dom_code);
CREATE INDEX        i_domprot__pi           ON domains_per_protein  (pi);
CREATE INDEX        i_domprot__dom_code     ON domains_per_protein  (dom_code);





-- --------------------------------------------------------
-- indices for managing tables

CREATE UNIQUE INDEX	i_acc__accid            ON accessions           (accid);
CREATE INDEX        i_secid__accid      	ON secondary_ids        (secid_accid);


-- --------------------------------------------------------
-- additional indices
--CREATE UNIQUE INDEX i_acc__db_prot_id_taxa ON accessions (prot_id, db_origin, tax_id);
CREATE INDEX i_taxa__division ON taxa (division);
CREATE INDEX i_taxa__name ON taxa (tax_name);
CREATE INDEX i_taxa__species ON taxa (species);
CREATE INDEX i_domprot__dom_name ON domains_per_protein (dom_name);
--CREATE INDEX      i_acc__prot_id  ON accessions (prot_id);
--CREATE INDEX		i_acc__acc_name ON accessions (acc_name);



GRANT SELECT ON TABLE accessions              TO GROUP select_panda;
GRANT SELECT ON TABLE archived_accessions     TO GROUP select_panda;
GRANT SELECT ON TABLE archived_domains        TO GROUP select_panda;
GRANT SELECT ON TABLE archived_hmms           TO GROUP select_panda;
GRANT SELECT ON TABLE archived_sequences      TO GROUP select_panda;
GRANT SELECT ON TABLE database_sources        TO GROUP select_panda;
GRANT SELECT ON TABLE domains                 TO GROUP select_panda;
GRANT SELECT ON TABLE domains_per_protein     TO GROUP select_panda;
GRANT SELECT ON TABLE hmms                    TO GROUP select_panda;
GRANT SELECT ON TABLE new_hmms                TO GROUP select_panda;
GRANT SELECT ON TABLE new_sequences           TO GROUP select_panda;
GRANT SELECT ON TABLE panda_parameters        TO GROUP select_panda;
GRANT SELECT ON TABLE pubmed_ids              TO GROUP select_panda;
GRANT SELECT ON TABLE secondary_ids           TO GROUP select_panda;
GRANT SELECT ON TABLE sequences               TO GROUP select_panda;
GRANT SELECT ON TABLE sequences_linked_tables TO GROUP select_panda;
GRANT SELECT ON TABLE taxa                    TO GROUP select_panda;
GRANT SELECT ON TABLE taxa_divisions          TO GROUP select_panda;
PL/SQLCMD
}


#_________________________________________________________________________________________

#	repopulate_tables

#_________________________________________________________________________________________
sub repopulate_tables
{
	open(FH_SQLCOPY, "| psql -q $host $username $password $dbname");
	my $base_dir = $FindBin::Bin;
	print FH_SQLCOPY <<"PL/SQLCMD";
-- 88888888888888888888888888888888888888888888888888888888

-- fill taxa_divisions

-- 88888888888888888888888888888888888888888888888888888888
DELETE FROM taxa_divisions;
INSERT INTO taxa_divisions VALUES (0,  'Bacteria');
INSERT INTO taxa_divisions VALUES (1,  'Invertebrates');
INSERT INTO taxa_divisions VALUES (2,  'Mammals');
INSERT INTO taxa_divisions VALUES (3,  'Phages');
INSERT INTO taxa_divisions VALUES (4,  'Plants');
INSERT INTO taxa_divisions VALUES (5,  'Primates');
INSERT INTO taxa_divisions VALUES (6,  'Rodents');
INSERT INTO taxa_divisions VALUES (7,  'Synthetic');
INSERT INTO taxa_divisions VALUES (8,  'Unassigned');
INSERT INTO taxa_divisions VALUES (9,  'Viruses');
INSERT INTO taxa_divisions VALUES (10, 'Vertebrates');

-- 88888888888888888888888888888888888888888888888888888888

-- fill sequences_linked_tables

--      tables dependent on the sequences table

-- 88888888888888888888888888888888888888888888888888888888
DELETE FROM sequences_linked_tables;
INSERT INTO sequences_linked_tables VALUES ('new_sequences');
INSERT INTO sequences_linked_tables VALUES ('domains_per_protein');
INSERT INTO sequences_linked_tables VALUES ('secondary_ids');
INSERT INTO sequences_linked_tables VALUES ('pubmed_ids');


INSERT INTO panda_parameters
(
seq_dir,
panda_cmd_line,
panda_scripts_dir,
db_scripts_dir,
panda_working_dir,
panda_dbname,
panda_usr_name,
panda_remote_host,
logfile
)
VALUES
(
'$base_dir/sequence_sources',
'panda',
'$base_dir',
'$base_dir/sequence_sources',
'$base_dir/..',
'$dbname',
'$user',
'',
'$base_dir/sequence_sources/logfile'
);


PL/SQLCMD

#INSERT INTO database_sources (
#	name,
#	description,
#	--depends,
#	remote_url,
#	db_update_interval,
#	local_file,
#--	check_up_to_date_cmds,
#	pre_process_cmds,
#	run_panda,
#	panda_param_fasta_file,
#	panda_param_regex,
#	panda_param_db_name,
#	panda_param_update_stats,
#	panda_param_misc
#--	post_process_cmds,
#	)
#
#VALUES
#('swissprot',
# 'curated protein sequence database at www.ebi.ac.uk/swissprot/',
# --'',
# 'ftp://ftp.ebi.ac.uk/pub/databases/sp_tr_nrdb/sprot.dat.gz',
# '15 days',
# '\$SEQ_DIR\$/sprot.dat.gz',
# --
# 'gunzip -f -c \$SEQ_DIR\$/sprot.dat.gz > \$SEQ_DIR\$/sprot.dat; \$SEQ_DIR\$/panda_sprot_to_fa.pl --d \$PANDA_DB_NAME\$ --u \$PANDA_USR_NAME\$ \$SEQ_DIR\$/sprot.dat > \$SEQ_DIR\$/sprot.dat.fa',
# TRUE,
# '\$SEQ_DIR\$/sprot.dat.fa',
# '[^\\|]+\\|([^\\|]+)\\|',
# 'sp',
# FALSE,
# '--verbose'
# --'/home/lg/panda/perl/bootstrap/process_sprot /home/lg/panda/perl/bootstrap/sprot.dat'
# );

 }

